package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.Course;
import com.xmy.cultivate.entity.OrderApply;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.response.Reconciliation;
import com.xmy.cultivate.entity.views.ResTransaction;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 订单报名信息 Mapper 接口
 * </p>
 *
 * @author min
 * @since 2022-07-28
 */
@Mapper
public interface OrderApplyMapper extends BaseMapper<OrderApply> {


    @Select("SELECT * FROM order_view ${ew.customSqlSegment}")
    public abstract IPage<OrderApply> findAll(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);


    /**
     * 欠费列表
     * @param wrapper
     * @return
     */
    @Select("select id,getDictName(order_type, 13) as orderTypeName,IFNULL(handle_person_name,'') as handlePersonName,handle_date as handleDate,getSchoolName(handle_school_id) as handleSchoolName,own_price as ownPrice from order_apply ${ew.customSqlSegment}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "course",
                    column = "id",
                    many = @Many(select = "com.xmy.cultivate.mapper.OrderApplyMapper.getOrderDetailListForOrderId")
            )
    })
    public abstract List<Map<String,Object>> orderArrears(@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select id,course_name from order_detail where order_id = #{orderId}")
    public abstract List<Map<String,Object>> getOrderDetailListForOrderId(Long orderId);





    @Select("select * from expenses_and_receipts_view ${ew.customSqlSegment}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "orderPayAccountList",
                    column = "id",
                    many = @Many(select = "com.xmy.cultivate.mapper.OrderPayAccountMapper.getListForOrderId")
            )
    })
    public abstract IPage<OrderApply> expensesAndReceipts(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("select * from transaction_view ${ew.customSqlSegment}")
    @Results({
            @Result(column = "order_id",property = "order_id"),
            @Result(
                    property = "orderPayAccountList",
                    column = "order_id",
                    many = @Many(select = "com.xmy.cultivate.mapper.OrderPayAccountMapper.getListForOrderId")
            )
    })
    public abstract IPage<ResTransaction> transaction(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select * from affirm_income_view ${ew.customSqlSegment}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "orderPayAccountList",
                    column = "id",
                    many = @Many(select = "com.xmy.cultivate.mapper.OrderPayAccountMapper.getListForOrderId")
            ),
            @Result(
                    property = "course",
                    column = "id",
                    many = @Many(select = "com.xmy.cultivate.mapper.OrderApplyMapper.getOrderDetailListForOrderId")
            )
    })
    public abstract IPage<OrderApply> affirmIncome(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select({"<script>",
            "SELECT ",
            "school_id,school_name,",
            "COUNT(id) AS total_records,",
            "SUM(CASE WHEN t.`status` = 0 THEN 1 ELSE 0 END) AS unchecked_records, ",
            "SUM(CASE WHEN t.`status` = 1 THEN 1 ELSE 0 END) AS checked_records ",
            "FROM ",
            "( ",
            "SELECT ",
            "a.*,d.`name` AS student_name, ",
            "COALESCE ( b.subjects_id, c.subjects_id ) AS subjects_id, ",
            "COALESCE ( b.school_id, c.school_id ) AS school_id, ",
            "getSchoolName (COALESCE ( b.school_id, c.school_id )) AS school_name ",
            "FROM ",
            "( ",
            "SELECT ",
            "t1.id,t1.receipt_no,t1.order_type,t1.apply_type,t1.`status`,t1.student_id,t1.handle_date,t2.old_order_detail_id ",
            "FROM ",
            "order_apply AS t1 ",
            "LEFT JOIN order_transfer_detail AS t2 ON t2.order_id = t1.id ",
            "WHERE ",
            "t1.lesson_type = 1 ",
            "AND t1.deleted = 0 ",
            "AND t1.`status` &lt;&gt; 2 ",
            "AND t1.order_type = 1 ",
            "AND t1.year_part = ${yearPart}",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND handle_date BETWEEN '${startDate}' AND '${endDate}' ",
            "GROUP BY t1.id ",
            ") AS a ",
            "LEFT JOIN order_detail AS b ON a.order_type = 4 AND a.old_order_detail_id = b.id ",
            "LEFT JOIN order_detail AS c ON a.order_type != 4 AND a.id = c.order_id ",
            "LEFT JOIN student AS d ON d.id = a.student_id ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "HAVING subjects_id = ${subjectsId} ",
            "</when>",
            ") AS t ",
            "GROUP BY school_id,school_name ",
            "</script>"})
    List<Reconciliation> reconciliationRate(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId,@Param("startDate") String startDate, @Param("endDate") String endDate);


    @Select({"<script>",
            "SELECT * FROM ( ",
            "SELECT",
            "a.*,d.`name` AS student_name, ",
            "COALESCE ( b.subjects_id, c.subjects_id ) AS subjects_id, ",
            "COALESCE ( b.school_id, c.school_id ) AS school_id, ",
            "getSchoolName (COALESCE ( b.school_id, c.school_id )) AS school_name ",
            "FROM ",
            "( ",
            "SELECT ",
            "t1.id,t1.receipt_no,t1.order_type,t1.apply_type,t1.`status`,t1.student_id,t1.handle_date,t2.old_order_detail_id ",
            "FROM ",
            "order_apply AS t1 ",
            "LEFT JOIN order_transfer_detail AS t2 ON t2.order_id = t1.id ",
            "WHERE ",
            "t1.lesson_type = 1 ",
            "AND t1.deleted = 0 ",
            "AND t1.`status` &lt;&gt; 2 ",
            "AND t1.order_type = 1 ",
            "AND t1.year_part = ${yearPart}",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.handle_date BETWEEN '${startDate}' AND '${endDate}' ",
            "<when test='status!=null and status!=&apos;&apos;'>",
            "AND t1.`status` = ${status} ",
            "</when>",
            "GROUP BY t1.id ",
            ") AS a ",
            "LEFT JOIN order_detail AS b ON a.order_type = 4 AND a.old_order_detail_id = b.id ",
            "LEFT JOIN order_detail AS c ON a.order_type != 4 AND a.id = c.order_id ",
            "LEFT JOIN student AS d ON d.id = a.student_id ",
            "HAVING school_id = ${schoolId} ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND subjects_id = ${subjectsId} ",
            "</when>",
            ") AS t ",
            "</script>"})
    IPage<Reconciliation> reconciliationDetails(IPage<Reconciliation> page,@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId,@Param("startDate") String startDate, @Param("endDate") String endDate, @Param("schoolId") String schoolId, @Param("status") String status);

}
